﻿Imports System.Data.OleDb
Imports System.Data
Imports System.Text
Imports System.IO

''' <summary>
''' CSV文件读写
''' </summary>
''' <remarks></remarks>
Public NotInheritable Class Csv

    Public Shared Encoding As Encoding = Encoding.GetEncoding("GB2312")
    Private Shared ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Text;{1}'"

#Region "Read"

    ''' <summary>
    ''' 读取CSV文件
    ''' </summary>
    ''' <param name="FilePath">CSV文件路径</param>
    ''' <param name="HDR">是否有列标题</param>
    ''' <returns>返回的DataTable</returns>
    ''' <remarks></remarks>
    Public Shared Function Read(ByVal FilePath As String, Optional ByVal HDR As Boolean = True) As DataTable
        Dim con As New OleDbConnection(String.Format(ConnectionString, Path.GetDirectoryName(FilePath), IIf(HDR, String.Empty, "HDR=no;")))
        Dim Name As String = Path.GetFileNameWithoutExtension(FilePath)
        Dim dt As New DataTable(Name)
        Using da As New OleDbDataAdapter(String.Format("select * from [{0}.csv]", Name), con)
            Try
                da.Fill(dt)
            Catch ex As OleDbException
                MsgBox(ex.Message)
            End Try
        End Using
        con.Dispose()
        Return dt
    End Function

    ''' <summary>
    ''' 读取CSV字符串
    ''' </summary>
    ''' <param name="CsvString">Csv格式字符串</param>
    ''' <returns>返回的DataTable</returns>
    ''' <remarks></remarks>
    Public Shared Function StringRead(ByVal CsvString As String) As DataTable
        Dim dt As New DataTable
        On Error Resume Next
        Dim str() As String = Split(CsvString, vbCrLf)
        Dim cs() As String = Split(str(0), ","c)
        For i = 0 To cs.Length - 1
            dt.Columns.Add(cs(i), GetType(String))
        Next
        For i = 1 To str.Length - 1
            Dim Cells() As String = Split(str(i), ","c)
            If Cells.Length <> dt.Columns.Count Then
                Continue For
            Else
                dt.Rows.Add(Split(str(i), ","c))
            End If
        Next
        'dt.PrimaryKey = New DataColumn() {dt.Columns(0)}
        Return dt
    End Function

    ''' <summary>
    ''' 读取CSV文件
    ''' </summary>
    ''' <param name="FilePath">CSV文件路径</param>
    ''' <returns>返回的DataTable</returns>
    ''' <remarks></remarks>
    Public Shared Function Read2(ByVal FilePath As String) As DataTable
        Using sr As New StreamReader(FilePath, Encoding)
            Return StringRead(sr.ReadToEnd)
            sr.Close()
        End Using
    End Function

#End Region

#Region "Write"

    ''' <summary>
    ''' 保存DataTable到CSV文件
    ''' </summary>
    ''' <param name="Path">CSV文件路径</param>
    ''' <param name="Table">要保存的DataTable</param>
    ''' <remarks></remarks>
    Public Shared Sub Save(ByVal Path As String, ByVal Table As DataTable)
        Using sw As New StreamWriter(Path, False, Encoding)
            sw.Write(CsvString(Table)) : sw.Flush() : sw.Close()
        End Using
    End Sub

    ''' <summary>
    ''' 保存DataRow数组到CSV文件
    ''' </summary>
    ''' <param name="Path">CSV文件路径</param>
    ''' <param name="Rows">要保存的DataRow数组</param>
    ''' <remarks></remarks>
    Public Shared Sub Save(ByVal Path As String, ByVal Rows() As DataRow)
        Using sw As New StreamWriter(Path, False, Encoding)
            sw.Write(CsvString(Rows)) : sw.Flush() : sw.Close()
        End Using
    End Sub

    ''' <summary>
    ''' 保存字符串到CSV文件
    ''' </summary>
    ''' <param name="Path">CSV文件路径</param>
    ''' <param name="s">要保存字符串</param>
    ''' <remarks></remarks>
    Public Shared Sub Save(ByVal Path As String, ByVal s As String)
        Using sw As New StreamWriter(Path, False, Encoding)
            sw.Write(s) : sw.Flush() : sw.Close()
        End Using
    End Sub

#End Region

#Region "Utils"

    Private Shared Function CsvString(ByVal Table As DataTable) As StringBuilder
        Dim str As New StringBuilder
        If Table.Columns.Count = 0 Then Return str
        For Each c As DataColumn In Table.Columns
            str.Append(CellString(c.ColumnName) & ",")
        Next
        str.Remove(str.Length - 1, 1).Append(vbCrLf)
        For Each r As DataRow In Table.Rows
            str.AppendLine(RowString(r))
        Next
        Return str
    End Function

    Private Shared Function CsvString(ByVal Rows() As DataRow) As StringBuilder
        Dim str As New StringBuilder
        For Each r As DataRow In Rows
            str.AppendLine(RowString(r))
        Next
        Return str
    End Function

    Private Shared Function RowString(ByVal Row As DataRow) As String
        Dim str As New StringBuilder
        For Each s In Row.ItemArray
            str.Append(CellString(s.ToString) & ",")
        Next
        Return str.Remove(str.Length - 1, 1).ToString
    End Function

    ''' <summary>
    ''' CSV字符串转换
    ''' </summary>
    ''' <param name="s">要转换的字符串</param>
    ''' <returns>返回转换后的字符串</returns>
    ''' <remarks></remarks>
    Public Shared Function CellString(ByVal s As String) As String
        If String.IsNullOrWhiteSpace(s) Then Return String.Empty
        Dim str As New StringBuilder(s)
        If IsAddChr34(s) Then
            str.Replace(Chr(34), Chr(34) & Chr(34))
            str.Insert(0, Chr(34))
            str.Append(Chr(34))
        End If
        Return str.ToString
    End Function

    Private Shared Function IsAddChr34(ByVal s As String) As Boolean
        Return s.IndexOf(Chr(10)) <> -1 OrElse s.IndexOf(Chr(13)) <> -1 _
        OrElse s.IndexOf(Chr(34)) <> -1 OrElse s.IndexOf(Chr(44)) <> -1
    End Function

#End Region

End Class